package com.webank.wedatasphere.qualitis.handler;

import com.webank.wedatasphere.qualitis.response.Grid;
import com.webank.wedatasphere.qualitis.response.Resp;
import com.webank.wedatasphere.qualitis.response.SelectRuleResponse;
import com.webank.wedatasphere.qualitis.response.TaskTableResponse;
import org.apache.commons.collections.CollectionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

@Component
public class CommonJDBCService {
    // 公共的JDBC服务

    @Autowired
    @Qualifier("jdbcTemplate")
    private JdbcTemplate jdbcTemplate;



    /**
     * 分页查询 规则颗粒度---细粒自定义
     */
    public Resp<?> queryRuleDataGridByPageInSqlWhere(Long projectId , int pageNumber, int pageSize){
        String querySql = "select b.* from qualitis_rule_relation_project a \n" +
                "left join qualitis_rule_new b on a.rule_id = b.id\n" +
                "where a.project_id = "+projectId +" order by a.id asc";
        String countSql=  "select count(1) from qualitis_rule_relation_project a \n" +
                "left join qualitis_rule_new b on a.rule_id = b.id\n" +
                "where a.project_id = "+projectId;
        int offset = (pageNumber - 1) * pageSize;
        querySql = querySql + " limit " + offset + " , " + pageSize;
        List<SelectRuleResponse> resultSets = jdbcTemplate.query(querySql, new Object[]{}, (resultSet, i) -> {
            SelectRuleResponse selectRuleResponse = new SelectRuleResponse();
            selectRuleResponse.setId(resultSet.getLong("id"));
            selectRuleResponse.setRuleTemplateName(resultSet.getString("rule_template_name"));
            selectRuleResponse.setCnName(resultSet.getString("rule_name"));
            selectRuleResponse.setDatasourceName(resultSet.getString("data_source_engine"));
            selectRuleResponse.setDbName(resultSet.getString("data_base_name"));
            selectRuleResponse.setTableName(resultSet.getString("table_name"));
            selectRuleResponse.setTableFields(resultSet.getString("field_name"));
            selectRuleResponse.setSqlWhere(resultSet.getString("filter_condition"));
            selectRuleResponse.setCreateTime(resultSet.getString("create_time"));
            return selectRuleResponse;
        });
        Integer total = null;
        try {
            total = jdbcTemplate.queryForObject(countSql, Integer.class);
        } catch (DataAccessException e) {
            total = 0;
        }
        return Resp.OkPageLayUI(resultSets, total == null ? 0 : total);

    }

    /**
     * 分页查询 规则颗粒度---细粒自定义
     */
    public Grid<?> queryTaskManyTableInSqlWhere(Long projectId , int pageNumber, int pageSize){
        String querySql = "select a.execute_status,a.begin_time , a.execute_time , a.end_time , a.project_id , a.sort , count(a.id) as total, bv.bvt as suctol, awbaw.bavt as nottal from qualitis_sql_task a left join ( select aw.project_id , aw.sort , count(aw.id) as bvt from qualitis_sql_task aw where aw.project_id = ? and aw.check_status = '校验通过' group by aw.sort) as bv on bv.project_id = a.project_id left join ( select baw.project_id , baw.sort , count(baw.id) as bavt from qualitis_sql_task baw where baw.project_id = ? and baw.check_status != '校验通过' group by baw.sort  ) as awbaw on awbaw.project_id = a.project_id where a.project_id = ? group by a.sort order by a.sort desc";
        String countSql=  "select count(a.sort) from qualitis_sql_task a left join ( select aw.project_id , aw.sort , count(aw.id) as bvt from qualitis_sql_task aw where aw.project_id = ? and aw.check_status = '校验通过') as bv on bv.project_id = a.project_id left join ( select baw.project_id , baw.sort , count(baw.id) as bavt from qualitis_sql_task baw where baw.project_id = ? and baw.check_status != '校验通过' ) as awbaw on awbaw.project_id = a.project_id where a.project_id = ? group by a.sort order by a.sort desc";
        int offset = (pageNumber - 1) * pageSize;
        querySql = querySql + " limit " + offset + " , " + pageSize;
        List<TaskTableResponse> resultSets = jdbcTemplate.query(querySql, new Object[]{projectId,projectId,projectId}, (resultSet, i) -> {
            TaskTableResponse taskItem = new TaskTableResponse();
            taskItem.setBeginTime(resultSet.getString("begin_time"));
            taskItem.setExecuteTime(resultSet.getString("execute_time"));
            taskItem.setEndTime(resultSet.getString("end_time"));
            taskItem.setProjectId(resultSet.getLong("project_id"));
            taskItem.setSort(resultSet.getInt("sort"));
            taskItem.setNottal(resultSet.getInt("nottal"));
            taskItem.setSuctol(resultSet.getInt("suctol"));
            taskItem.setTotal(resultSet.getInt("total"));
            taskItem.setExecuteStatus(resultSet.getString("execute_status"));
            return taskItem;
        });
        Integer total = null;
        try {
            total = jdbcTemplate.queryForObject(countSql,new Object[]{projectId,projectId,projectId}, Integer.class);
        } catch (DataAccessException e) {
            total = 0;
        }
        return Grid.OkPage(CollectionUtils.isEmpty(resultSets) ?new ArrayList<>():resultSets, total == null ? 0 : total);

    }

}
